/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Model;

import Entity.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import library.Koneksi;

/**
 *
 * @author mr2
 */
public class BimbinganTAModel {
    
    Connection koneksi;
    BimbinganTA bimbinganTA;
    
    public BimbinganTAModel(){
        koneksi = Koneksi.getKoneksi();
    }
    
    public List<BimbinganTA> ListBimbinganTA(String idMahasiswa){
        
        PreparedStatement prepare = null;
        ResultSet result = null;
        List<BimbinganTA> listBimbinganMhs = new ArrayList<BimbinganTA>();

        
        try{
            String sql = "SELECT id_bim, tgl_bim, judul_bim, bimbingan_ta.id_user, status_bim FROM bimbingan_ta, ta "
                    + "WHERE bimbingan_ta.id_ta = ta.id_ta "
                    + "AND ta.status_ta != '0' "
                    + "AND ta.id_user = ? ";
                    
            prepare = koneksi.prepareStatement(sql);
            
            prepare.setString(1, idMahasiswa);
            
            result = prepare.executeQuery();
                       
            while(result.next()){
                
                BimbinganTA bimbinganTA = new BimbinganTA();
                User user = new User();
                user.setIdUser(result.getString("id_user"));
                
                bimbinganTA.setUser(user);
                bimbinganTA.setIdBimbingan(result.getInt("id_bim"));
                bimbinganTA.setJudulBimbingan(result.getString("judul_bim"));
                bimbinganTA.setTglBimbingan(result.getString("tgl_bim"));
                bimbinganTA.setStatusBimbingan(result.getString("status_bim"));
                listBimbinganMhs.add(bimbinganTA);
            }
            return listBimbinganMhs;
            
        }catch(SQLException ex){          
            System.out.println(ex.getMessage());
            return listBimbinganMhs;
        }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
            if(result != null){
                try{
                    result.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
        }
    }
    
    //Model insert pendaftaran sidang Proposal
    public boolean CatatBimbingan(BimbinganTA bimbinganta){
        
        boolean hasil;
        PreparedStatement prepare = null;
        try{
            String sql = "INSERT INTO bimbingan_ta(id_ta, id_user, judul_bim, isi_bim, tgl_bim)"
                    + "values(?,?,?,?,?)";
            
            prepare = koneksi.prepareStatement(sql);
            
            prepare.setInt(1, bimbinganta.getTa().getIdTA());
            prepare.setString(2, bimbinganta.getUser().getIdUser());
            prepare.setString(3, bimbinganta.getJudulBimbingan());
            prepare.setString(4, bimbinganta.getIsiBimbingan());
            prepare.setString(5, bimbinganta.getTglBimbingan());
            
            int status = prepare.executeUpdate();
            if(status > 0){
                hasil = true;
            }else{
                hasil = false;
            }
            
            
        }catch(SQLException ex){
            hasil = false;
            System.out.println(ex.getMessage());
        }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
        }
        
        return hasil;
    }
    
    public List<TA> ListMhsBimbingan(String idDosen, String statusPembimbing){
        
        PreparedStatement prepare = null;
        ResultSet result = null;
        List<TA> listMhsBimbingan = new ArrayList<TA>();
        
        
        try{
            String sql = null;
            if("PEMBIMBING1".equals(statusPembimbing)){
                sql = "SELECT distinct ta.id_ta, ta.id_user, user.nama_user "
                    + "FROM ta, user "
                    + "WHERE ta.id_ta NOT IN (select ta_final.id_ta from ta_final, ta where status_sidang_final = 'LULUS' and  ta.status_ta = '1' and ta.id_ta = ta_final.id_ta) "
                    + "and pembimbing_1 = ? AND ta.id_user = user.id_user ";
            }else{
                sql = "SELECT distinct ta.id_ta, ta.id_user, user.nama_user "
                    + "FROM ta, user "
                    + "WHERE ta.id_ta NOT IN (select ta_final.id_ta from ta_final, ta where status_sidang_final = 'LULUS' and  ta.status_ta = '1' and ta.id_ta = ta_final.id_ta) "
                    + "and pembimbing_2 = ? AND ta.id_user = user.id_user ";
            }
            
            prepare = koneksi.prepareStatement(sql);
            
            prepare.setString(1, idDosen);
            
            result = prepare.executeQuery();
                       
            while(result.next()){
                
                SidangTA MhsBimbingan = new SidangTA();
                User user = new User();
                user.setIdUser(result.getString("id_user"));
                user.setNamaUser(result.getString("nama_user"));
                
                TA ta = new TA();
                ta.setUser(user);
                ta.setIdTA(result.getInt("id_ta"));
                
                listMhsBimbingan.add(ta);
            }
            return listMhsBimbingan;
            
        }catch(SQLException ex){          
            System.out.println(ex.getMessage());
            return listMhsBimbingan;
        }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
            if(result != null){
                try{
                    result.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
        }
    }
    
    public List<BimbinganTA> DosenListBimbinganTAMhs(String idDosen, int idTA){
        
        PreparedStatement prepare = null;
        ResultSet result = null;
        List<BimbinganTA> listBimbinganMhs = new ArrayList<BimbinganTA>();

        
        try{
            String sql = "SELECT id_bim, tgl_bim, judul_bim, status_bim, bimbingan_ta.id_user  "
                    + "FROM ta, bimbingan_ta "
                    + "WHERE ta.id_ta = bimbingan_ta.id_ta "
                    + "AND bimbingan_ta.id_user = ? and ta.id_ta = ? "
                    + "AND ta.status_ta = '1'";
                    
            prepare = koneksi.prepareStatement(sql);
            
            prepare.setString(1, idDosen);
            prepare.setInt(2, idTA);
            
            result = prepare.executeQuery();
                       
            while(result.next()){
                User user = new User();
                user.setIdUser("id_user");
                
                BimbinganTA bimbinganTA = new BimbinganTA();
                bimbinganTA.setIdBimbingan(result.getInt("id_bim"));
                bimbinganTA.setUser(user);
                bimbinganTA.setJudulBimbingan(result.getString("judul_bim"));
                bimbinganTA.setTglBimbingan(result.getString("tgl_bim"));
                bimbinganTA.setStatusBimbingan(result.getString("status_bim"));
                listBimbinganMhs.add(bimbinganTA);
            }
            return listBimbinganMhs;
            
        }catch(SQLException ex){          
            System.out.println(ex.getMessage());
            return listBimbinganMhs;
        }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
            if(result != null){
                try{
                    result.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
        }
    }
    
    public BimbinganTA DetailBimbingan(String idBim){
        PreparedStatement prepare = null;
        ResultSet result = null;
        BimbinganTA bimbinganTA = null;
        
        try{
            String sql = "SELECT * from bimbingan_ta where id_bim = ?";
            
            prepare = koneksi.prepareStatement(sql);

            prepare.setString(1,idBim);
            
            result = prepare.executeQuery();
            
            if(result.next()){
                bimbinganTA = new BimbinganTA();
                User user = new User();
                
                user.setIdUser(result.getString("id_user"));
                bimbinganTA.setUser(user);
                bimbinganTA.setIdBimbingan(result.getInt("id_bim"));
                bimbinganTA.setJudulBimbingan(result.getString("judul_bim"));
                bimbinganTA.setIsiBimbingan(result.getString("isi_bim"));
                bimbinganTA.setStatusBimbingan(result.getString("status_bim"));
                bimbinganTA.setTglBimbingan(result.getString("tgl_bim"));       
            }
            return bimbinganTA;
            
        }catch(SQLException ex){
            return bimbinganTA;
        }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){
                    
                }
            }
            if(result != null){
                try{
                    result.close();
                }catch(SQLException ex){
                    
                }
            }
        
        }
    }
    
   //Model melakukan update bimbingan mahasiswa
   public boolean UpdateDataBimbingan(BimbinganTA bimbinganTA){
        PreparedStatement prepare = null;
        boolean hasil = true;
        try{            
            
            String sql = "update bimbingan_ta set judul_bim = ?, isi_bim = ? where id_bim = ?";
            
            prepare = koneksi.prepareStatement(sql);
            
            prepare.setString(1, bimbinganTA.getJudulBimbingan());
            prepare.setString(2, bimbinganTA.getIsiBimbingan());
            prepare.setInt(3, bimbinganTA.getIdBimbingan());
            
            int status = prepare.executeUpdate();
            
            if(status > 0){
                hasil = true;
            }else{
                hasil = false;
            }
            
            System.out.println(hasil);
            
        }catch(SQLException ex){
            hasil = false;
            System.out.println(ex.getMessage());
        }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){                   
                }
            }
        }
        return hasil;
   }
   
   //Model melakukan update bimbingan mahasiswa
   public void setujuDataBimbingan(BimbinganTA bimbinganTA){
        PreparedStatement prepare = null;
        try{            
            
            String sql = "update bimbingan_ta set status_bim = ? where id_bim = ?";
            
            prepare = koneksi.prepareStatement(sql);
            
            prepare.setString(1, bimbinganTA.getStatusBimbingan());
            prepare.setInt(2, bimbinganTA.getIdBimbingan());
            
            prepare.executeUpdate();
            
        }catch(SQLException ex){
            System.out.println(ex.getMessage());
        }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){                   
                }
            }
        }
   }
   
   //Menampilkan rata - rata jumlah bimbingan mahasiswa pada dosen
   public double RataJLHMhsPerDosen(String periodeRekapAwal, String periodeRekapAkhir){
       
       PreparedStatement prepare = null;
       ResultSet result = null;
       
       double rataJlhMhs = 0;
        
        try {
               
            String sql = "SELECT avg(jlhMhs) as rata2jlhMhs "
                    + "FROM (SELECT DISTINCT dosen.id_user, COUNT(idTA) as JlhMhs "
                    + "FROM dosen LEFT JOIN (SELECT DISTINCT ta.id_ta as idTA , ta.pembimbing_1 AS p1, ta.pembimbing_2 AS p2 "
                    + "FROM ta, ta_proposal, ta_final "
                    + "WHERE (ta.status_ta = '1' AND ta.id_ta = ta_proposal.id_ta AND proposal_tgl_sidang BETWEEN ? AND ? ) OR "
                    + "(ta.status_ta = '1' AND ta.id_ta = ta_final.id_ta AND final_tgl_sidang between ? and ? )) AS temp  ON (dosen.id_user = p1 OR dosen.id_user = p2) "
                    + "WHERE dosen.status_dosen = 'TETAP' GROUP BY dosen.id_user ) as tabelJumlah ";
            
            
            prepare = koneksi.prepareStatement(sql);
            
            //proposal
           prepare.setString(1, periodeRekapAwal);
           prepare.setString(2, periodeRekapAkhir);
           
           //final
           prepare.setString(3, periodeRekapAwal);
           prepare.setString(4, periodeRekapAkhir);
            
            result = prepare.executeQuery();
            
             if(result.next()){
                 rataJlhMhs = result.getDouble("rata2jlhMhs");
             }else{
                 
             }
             
        }catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
            if(result != null){
                try{
                    result.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
        }
        return rataJlhMhs;
   }
   
   public int JlhMhsPembimbing1(String idDosen){
      
       PreparedStatement prepare = null;
       ResultSet result = null;
       int jlhMhsPembimbing1 = 0;
       
       try{
           String sql = " Select count(MhsBimbingan) as jumlahBimbingan1  from (SELECT distinct ta.id_ta as MhsBimbingan, ta.id_user, user.nama_user "
                   + "FROM ta, user "
                   + "WHERE ta.id_ta NOT IN (select ta_final.id_ta from ta_final, ta where status_sidang_final = 'LULUS' and  ta.status_ta = '1' and ta.id_ta = ta_final.id_ta) "
                   + "and pembimbing_1 = ? AND ta.id_user = user.id_user) as temp ";
           
           prepare = koneksi.prepareStatement(sql);
           
           //proposal
           prepare.setString(1, idDosen);
           
           result = prepare.executeQuery();
           
           if(result.next()){
                
                jlhMhsPembimbing1 = result.getInt("jumlahBimbingan1");
            }else{
                jlhMhsPembimbing1 = 0;
            }
           
            return jlhMhsPembimbing1;
           
       }catch(SQLException ex){
           System.out.println(ex.getMessage());
           return jlhMhsPembimbing1;
       }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
            if(result != null){
                try{
                    result.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
        } 
   }
   
   public int JlhMhsPembimbing2(String idDosen){
      
       PreparedStatement prepare = null;
       ResultSet result = null;
       int jlhMhsPembimbing2 = 0;
       
       try{
           String sql = " Select count(MhsBimbingan) as jumlahBimbingan1  from (SELECT distinct ta.id_ta as MhsBimbingan, ta.id_user, user.nama_user "
                   + "FROM ta, user "
                   + "WHERE ta.id_ta NOT IN (select ta_final.id_ta from ta_final, ta where status_sidang_final = 'LULUS' and  ta.status_ta = '1' and ta.id_ta = ta_final.id_ta) "
                   + "and pembimbing_2 = ? AND ta.id_user = user.id_user) as temp ";
           
           prepare = koneksi.prepareStatement(sql);
           
           //proposal
           prepare.setString(1, idDosen);
           
           result = prepare.executeQuery();
           
           if(result.next()){
                
                jlhMhsPembimbing2 = result.getInt("jumlahBimbingan1");
            }else{
                jlhMhsPembimbing2 = 0;
            }
            return jlhMhsPembimbing2;
           
       }catch(SQLException ex){
           System.out.println(ex.getMessage());
           return jlhMhsPembimbing2;
       }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
            if(result != null){
                try{
                    result.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
        } 
   }
   
   //Jumlah mahasiswa bimingan dosen yang lulus pada periode tertentu
   public ArrayList JlhMhsBimbinganDosenLulus(String periodeRekapAwal, String periodeRekapAkhir){
      
       PreparedStatement prepare = null;
       ResultSet result = null;
       ArrayList listJlhBimbinganMhs = new ArrayList<Dosen>();
       
       try{
           String sql = "SELECT DISTINCT dosen.id_user, COUNT(idTA) as JlhMhs "
                   + "FROM dosen LEFT JOIN (SELECT DISTINCT ta.id_ta as idTA , ta.pembimbing_1 AS p1, ta.pembimbing_2 AS p2 FROM ta, ta_proposal, ta_final WHERE "
                   + "(ta.status_ta <> '0' AND ta.id_ta = ta_proposal.id_ta AND proposal_tgl_sidang BETWEEN ? AND ? ) OR "
                   + "(ta.status_ta <> '0' AND ta.id_ta = ta_final.id_ta AND final_tgl_sidang between ? and ? )) AS temp  ON (dosen.id_user = p1 OR dosen.id_user = p2) "
                   + "WHERE dosen.status_dosen = 'TETAP' "
                   + "GROUP BY dosen.id_user";
           
           prepare = koneksi.prepareStatement(sql);
           
           //proposal
           prepare.setString(1, periodeRekapAwal);
           prepare.setString(2, periodeRekapAkhir);
           
           //final
           prepare.setString(3, periodeRekapAwal);
           prepare.setString(4, periodeRekapAkhir);
           
           result = prepare.executeQuery();
           
           while(result.next()){
                
                Map map = new HashMap();
                map.put("idUser", result.getString("id_user"));
                map.put("jlhBimbingan", result.getString("JlhMhs"));
                
                listJlhBimbinganMhs.add(map);
            }
            return listJlhBimbinganMhs;
           
       }catch(SQLException ex){
           System.out.println(ex.getMessage());
           return listJlhBimbinganMhs;
       }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
            if(result != null){
                try{
                    result.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
        } 
   }
   
   //Melihat detail siapa saja bimbingan dosen yang lulus pada periode tertentu
   public List<TA> MhsBimbinganDosenLulus(String idDosen, String periodeRekapAwal, String periodeRekapAkhir){
      
       PreparedStatement prepare = null;
       ResultSet result = null;
       List<TA> listJlhBimbinganMhs = new ArrayList<TA>();
       
       try{
           String sql = "SELECT dosen.id_user, idTA, idUser "
                   + "FROM dosen RIGHT JOIN (SELECT DISTINCT ta.id_ta as idTA, ta.id_user as idUser, ta.pembimbing_1 AS p1, ta.pembimbing_2 AS p2 FROM ta, ta_proposal, ta_final "
                   + "WHERE "
                   + "(ta.status_ta <> '0' AND ta.id_ta = ta_proposal.id_ta AND proposal_tgl_sidang BETWEEN ? AND ? ) OR "
                   + "(ta.status_ta <> '0' AND ta.id_ta = ta_final.id_ta AND final_tgl_sidang between ? and ? )) AS temp  ON (dosen.id_user = p1 OR dosen.id_user = p2) "
                   + "WHERE dosen.status_dosen = 'TETAP' and dosen.id_user = ? ";
           
           prepare = koneksi.prepareStatement(sql);
           
           //proposal
           prepare.setString(1, periodeRekapAwal);
           prepare.setString(2, periodeRekapAkhir);
           
           //final
           prepare.setString(3, periodeRekapAwal);
           prepare.setString(4, periodeRekapAkhir);
           
           //id_Dosen
           prepare.setString(5, idDosen);
           
           result = prepare.executeQuery();
           
           while(result.next()){
                
                TA ta = new TA();
                User user = new User();
                user.setIdUser(result.getString("idUser"));
                ta.setUser(user);

                listJlhBimbinganMhs.add(ta);
            }
            return listJlhBimbinganMhs;
           
       }catch(SQLException ex){
           System.out.println(ex.getMessage());
           return listJlhBimbinganMhs;
       }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
            if(result != null){
                try{
                    result.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
        } 
   }
   
   //Menampilkan rata - rata lama waktu mahasiswa dalam menyelesaikan tugas akhir.
   public double RataLamaTA(String periodeRekapAwal, String periodeRekapAkhir){
       
       PreparedStatement prepare = null;
       ResultSet result = null;
       
       double RataLamaTA = 0;
        
        try {
               
            String sql = "SELECT AVG(DATEDIFF(final_tgl_sidang, proposal_tgl_sidang))/30 AS RATA2LAMATA "
                    + "FROM ( "
                    + "SELECT ta.id_ta, proposal_tgl_sidang, final_tgl_sidang "
                    + "FROM ta, ta_proposal, ta_final "
                    + "WHERE ta.status_ta <> '0' AND ta.id_ta = ta_proposal.id_ta AND ta.id_ta = ta_final.id_ta "
                    + "AND final_tgl_sidang BETWEEN ? AND ? AND (status_sidang_final = 'LULUS' OR status_sidang_final = 'LULUS DENGAN REVISI' ) "
                    + "AND (status_sidang_proposal = 'DITERIMA' OR status_sidang_proposal = 'DITERIMA DENGAN REVISI')) AS temp";
            
            prepare = koneksi.prepareStatement(sql);
            prepare.setString(1,periodeRekapAwal);
            prepare.setString(2,periodeRekapAkhir);
            
            result = prepare.executeQuery();     
            
            
             if(result.next()){
                 RataLamaTA = result.getDouble("RATA2LAMATA");
             }
             
        }catch (SQLException ex) {
            System.out.println(ex.getMessage()); 
        }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
            if(result != null){
                try{
                    result.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
        }
        return RataLamaTA;
   }
   
//   public List<TA> ListLamaTA(String periodeRekapAwal, String periodeRekapAkhir){
//      
//       PreparedStatement prepare = null;
//       ResultSet result = null;
//       List<TA> listPertemuan = new ArrayList<TA>();
//       
//       try{
//           String sql = "SELECT idUser, DATEDIFF(final_tgl_diterima, proposal_tgl_diterima)/30 AS LAMATA "
//                   + "FROM "
//                   + "( SELECT ta.id_ta, ta.id_user as idUser, proposal_tgl_diterima, final_tgl_diterima FROM ta, ta_proposal, ta_final "
//                   + "WHERE ta.status_ta <> '0' AND ta.id_ta = ta_proposal.id_ta AND ta.id_ta = ta_final.id_ta "
//                   + "AND final_tgl_diterima BETWEEN ? AND ? AND status_sidang_final = 'LULUS' AND status_sidang_proposal = 'DITERIMA') AS temp";
//           
//           prepare = koneksi.prepareStatement(sql);
//           
//           //proposal
//           prepare.setString(1, periodeRekapAwal);
//           prepare.setString(2, periodeRekapAkhir);          
//           
//           result = prepare.executeQuery();
//           
//           while(result.next()){
//                
//                TA ta = new TA();
//                User user = new User();
//                user.setIdUser(result.getString("idUser"));
//                
//                ta.setUser(user);
//                ta.setLamaTA(result.getDouble("LAMATA"));
//
//                listPertemuan.add(ta);
//            }
//            return listPertemuan;
//           
//       }catch(SQLException ex){
//           System.out.println(ex.getMessage());
//           return listPertemuan;
//       }finally{
//            if(prepare != null){
//                try{
//                    prepare.close();
//                }catch(SQLException ex){
//                    System.out.println(ex.getMessage());
//                }
//            }
//            if(result != null){
//                try{
//                    result.close();
//                }catch(SQLException ex){
//                    System.out.println(ex.getMessage());
//                }
//            }
//        } 
//   }
   
   public ArrayList ListLamaTA(String periodeRekapAwal, String periodeRekapAkhir){
      
       PreparedStatement prepare = null;
       ResultSet result = null;
       ArrayList listPertemuan = new ArrayList<TA>();
       
       try{
           String sql = "SELECT idUser, DATEDIFF(final_tgl_sidang, proposal_tgl_sidang)/30 AS LAMATA "
                   + "FROM "
                   + "( SELECT ta.id_ta, ta.id_user as idUser, proposal_tgl_sidang, final_tgl_sidang FROM ta, ta_proposal, ta_final "
                   + "WHERE ta.status_ta <> '0' AND ta.id_ta = ta_proposal.id_ta AND ta.id_ta = ta_final.id_ta "
                   + "AND final_tgl_sidang BETWEEN ? AND ? AND (status_sidang_final = 'LULUS' OR status_sidang_final = 'LULUS DENGAN REVISI' ) AND (status_sidang_proposal = 'DITERIMA' OR status_sidang_proposal = 'DITERIMA DENGAN REVISI')) AS temp";
           
           prepare = koneksi.prepareStatement(sql);
           
           //proposal
           prepare.setString(1, periodeRekapAwal);
           prepare.setString(2, periodeRekapAkhir);          
           
           result = prepare.executeQuery();
           
           while(result.next()){
                
                Map map = new HashMap();
                map.put("idUser",result.getString("idUser"));
                map.put("lamata",result.getDouble("LAMATA"));

                listPertemuan.add(map);
            }
            return listPertemuan;
           
       }catch(SQLException ex){
           System.out.println(ex.getMessage());
           return listPertemuan;
       }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
            if(result != null){
                try{
                    result.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
        } 
   }
   
   //Menampilkan rata - rata pertemuan mahasiswa dosen untuk menyelesaikan tugas akhir
   public double RataPertemuan(String periodeRekapAwal, String periodeRekapAkhir){
       
       PreparedStatement prepare = null;
       ResultSet result = null;
       
       double rataPertemuan = 0;
        
        try {
               
            String sql = "select avg(jlhPertemuan) as rataPertemuan from "
                       + "(select ta.id_ta, ta.id_user, count(idBim) as jlhPertemuan "
                       + "from ta left join ( select distinct ta.id_ta as idTA, bimbingan_ta.id_bim as idBim from ta, ta_final, bimbingan_ta "
                       + "where ta.id_ta = bimbingan_ta.id_ta and ta.id_ta = ta_final.id_ta and bimbingan_ta.status_bim = 'OK' and ta.status_ta = '1' ) as temp on ta.id_ta = idTA, ta_final "
                       + "where ta.id_ta = ta_final.id_ta and final_tgl_sidang between ? and ? "
                       + "group by ta.id_ta) as tblJlhBimbingan";
            
            prepare = koneksi.prepareStatement(sql);
            
            prepare.setString(1,periodeRekapAwal);
            prepare.setString(2,periodeRekapAkhir);
            
            result = prepare.executeQuery();           
            
             if(result.next()){
                 rataPertemuan = result.getDouble("rataPertemuan");
             }else{
                 rataPertemuan = 0;
             }
             
        }catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
            if(result != null){
                try{
                    result.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
        }
        return rataPertemuan;
   }
   
//   public List<TA> ListJlhPertemuan(String periodeRekapAwal, String periodeRekapAkhir){
//      
//       PreparedStatement prepare = null;
//       ResultSet result = null;
//       List<TA> listPertemuan = new ArrayList<TA>();
//       
//       try{
//           String sql = "select ta.id_ta, ta.id_user, count(idBim) as jlhPertemuan "
//                   + "from ta left join ( select distinct ta.id_ta as idTA, bimbingan_ta.id_bim as idBim from ta, ta_final, bimbingan_ta "
//                   + "where ta.id_ta = bimbingan_ta.id_ta and ta.id_ta = ta_final.id_ta and bimbingan_ta.status_bim = 'OK' and ta.status_ta = '1' ) as temp on ta.id_ta = idTA, ta_final "
//                   + "where ta.id_ta = ta_final.id_ta and final_tgl_diterima between ? and ? "
//                   + "group by ta.id_ta";
//           
//           prepare = koneksi.prepareStatement(sql);
//           
//           //proposal
//           prepare.setString(1, periodeRekapAwal);
//           prepare.setString(2, periodeRekapAkhir);          
//           
//           result = prepare.executeQuery();
//           
//           while(result.next()){
//                
//                TA ta = new TA();
//                User user = new User();
//                user.setIdUser(result.getString("id_user"));
//                
//                ta.setUser(user);
//                ta.setJlhBimbingan(result.getInt("jlhPertemuan"));
//
//                listPertemuan.add(ta);
//            }
//            return listPertemuan;
//           
//       }catch(SQLException ex){
//           System.out.println(ex.getMessage());
//           return listPertemuan;
//       }finally{
//            if(prepare != null){
//                try{
//                    prepare.close();
//                }catch(SQLException ex){
//                    System.out.println(ex.getMessage());
//                }
//            }
//            if(result != null){
//                try{
//                    result.close();
//                }catch(SQLException ex){
//                    System.out.println(ex.getMessage());
//                }
//            }
//        } 
//   }
   
   public ArrayList ListJlhPertemuan(String periodeRekapAwal, String periodeRekapAkhir){
      
       PreparedStatement prepare = null;
       ResultSet result = null;
       ArrayList listPertemuan = new ArrayList<TA>();
       
       try{
           String sql = "select ta.id_ta, ta.id_user, count(idBim) as jlhPertemuan "
                   + "from ta left join ( select distinct ta.id_ta as idTA, bimbingan_ta.id_bim as idBim from ta, ta_final, bimbingan_ta "
                   + "where ta.id_ta = bimbingan_ta.id_ta and ta.id_ta = ta_final.id_ta and bimbingan_ta.status_bim = 'OK' and ta.status_ta = '1' ) as temp on ta.id_ta = idTA, ta_final "
                   + "where ta.id_ta = ta_final.id_ta and final_tgl_sidang between ? and ? "
                   + "group by ta.id_ta";
           
           prepare = koneksi.prepareStatement(sql);
           
           //proposal
           prepare.setString(1, periodeRekapAwal);
           prepare.setString(2, periodeRekapAkhir);          
           
           result = prepare.executeQuery();
           
           while(result.next()){
                
                Map map = new HashMap();
                map.put("idUser", result.getString("id_user"));
                map.put("jlhPertemuan", result.getInt("jlhPertemuan"));

                listPertemuan.add(map);
            }
            return listPertemuan;
           
       }catch(SQLException ex){
           System.out.println(ex.getMessage());
           return listPertemuan;
       }finally{
            if(prepare != null){
                try{
                    prepare.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
            if(result != null){
                try{
                    result.close();
                }catch(SQLException ex){
                    System.out.println(ex.getMessage());
                }
            }
        } 
   }
   
    
}
